package com.bw.mapper;

import com.bw.bean.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select ci.chapter_name name,count(u.chapter_id)num1,sum(u.position_sec)time,\n" +
            "count(distinct user_id)num2,sum(u.position_sec)/count(distinct user_id)avg from user_chapter_process u left join chapter_info ci on u.chapter_id = ci.id\n" +
            "group by ci.chapter_name")
    List<Vo1> list1();
    @Select("select '[0,60)'name,sum(case when t.score<60 then 1 else 0 end)value from test_exam t left join test_paper tp on t.paper_id = tp.id\n" +
            "union\n" +
            "select '[60,80)'name,sum(case when t.score>=60 and t.score<80 then 1 else 0 end)value from test_exam t left join test_paper tp on t.paper_id = tp.id\n" +
            "union\n" +
            "select '[80,90)'name,sum(case when t.score>=80 and t.score<90 then 1 else 0 end)value from test_exam t left join test_paper tp on t.paper_id = tp.id\n" +
            "union\n" +
            "select '[90,95)'name,sum(case when t.score>=90 and t.score<95 then 1 else 0 end)value from test_exam t left join test_paper tp on t.paper_id = tp.id\n" +
            "union\n" +
            "select '[95,100]'name,sum(case when t.score>=95 and t.score<=100 then 1 else 0 end)value from test_exam t left join test_paper tp on t.paper_id = tp.id")
    List<Vo2> list2();
    @Select("select bp.name name,sum(o.final_amount)value from order_info o left join base_province bp on o.province_id = bp.id\n" +
            "group by bp.name")
    List<Vo2> list3();
    @Select("select hour(create_time)name,count(user_id)value from user_chapter_process group by hour(create_time) order by hour(create_time)")
    List<Vo2> list4();
    @Select("select bs.source_site name,sum(o.final_amount)price,count(distinct o.user_id)usernum,count(*)ddnum from order_info o left join order_detail od on o.id = od.order_id\n" +
            "left join course_info ci on od.course_id = ci.id\n" +
            "left join chapter_info c on ci.id = c.course_id\n" +
            "left join video_info vi on c.id = vi.chapter_id\n" +
            "left join base_source bs on vi.video_source_id = bs.id\n" +
            "group by bs.source_site")
    List<Vo3> list5();
    @Select("select ci.course_name name,count(distinct t.user_id)num,avg(t.score)avg,avg(t.duration_sec)time from test_exam t left join test_paper tp on t.paper_id = tp.id\n" +
            "left join course_info ci on tp.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo4> list6();
    @Select("select ci.course_name name,avg(r.review_stars)avg,count(r.id)num1,sum(case when r.review_stars>=5 then 1 else 0 end)num2,\n" +
            "sum(case when r.review_stars>=5 then 1 else 0 end)/count(r.review_stars)hpl from review_info r left join course_info ci on r.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo5> list7();
    @Select("select count(*) from order_info where create_time>'2021-04-23'")
    Integer list8();
}
